| |
4981 Search Results Found |
| 1 |
Forum: Text & interMedia «»
Posted on: Tue, 15 August 2023 16:36 «»
By: Barbara Boehmer
Re: How to determine last tiime Content was indexed
…s. I granted the privileges to user C##SCOTT on my system. You will need to have the privilege granted to the creator fo the index, presumably you. The second script you can run from your own schema that you have been running everything …
|
| 2 |
Forum: Text & interMedia «»
Posted on: Mon, 14 August 2023 15:24 «»
By: Barbara Boehmer
Re: How to determine last tiime Content was indexed
…which it is before continuing.
C##SCOTT@XE_21.3.0.0.0> -- create table, url_datastore preference, and index:
C##SCOTT@XE_21.3.0.0.0> CREATE TABLE test_token
2 (assigned_id VARCHAR2(11),
3 …
|
| 3 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 05 April 2023 16:52 «»
By: Barbara Boehmer
Re: difficult task
…for the remaining columns.
SCOTT@orcl_12.1.0.2.0> CREATE TABLE temp_positions(
2 id_share INT,
3 n INT,
4 value NUMBER,
5 p_average NUMBER,
6 p_result_realized NUMBER,
7 p_result_unrealized NUMBER
8 )
9…
|
| 4 |
Forum: SQL & PL/SQL «»
Posted on: Tue, 28 February 2023 13:32 «»
By: Barbara Boehmer
Re: how to incrementally get all numbers incremented by n between 2 columns values
…tests of various number values for n.
SCOTT@orcl_12.1.0.2.0> -- test_data:
SCOTT@orcl_12.1.0.2.0> select * from test_data order by instance_number, snap_id
2 /
INSTANCE_NUMBER SNAP_ID
--------------- ----------
1 …
|
| 5 |
Forum: SQL & PL/SQL «»
Posted on: Tue, 28 February 2023 13:30 «»
By: Barbara Boehmer
Re: how to incrementally get all numbers incremented by n between 2 columns values
… n, including 1, which returns no rows.
SCOTT@orcl_12.1.0.2.0> -- test_data:
SCOTT@orcl_12.1.0.2.0> select * from test_data order by instance_number, snap_id
2 /
INSTANCE_NUMBER SNAP_ID
--------------- ----------
1…
|
| 6 |
Forum: Text & interMedia «»
Posted on: Tue, 15 August 2023 03:17 «»
By: Barbara Boehmer
Re: How to determine last tiime Content was indexed
…dropped, so that can be ignored.
C##SCOTT@XE_21.3.0.0.0> commit;
Commit complete.
C##SCOTT@XE_21.3.0.0.0> DROP TABLE KJC_TEST_INDEX;
Table dropped.
C##SCOTT@XE_21.3.0.0.0> CREATE TABLE KJC_TEST_INDEX
2 &…
|
| 7 |
Forum: SQL & PL/SQL «»
Posted on: Mon, 05 February 2024 22:58 «»
By: Barbara Boehmer
Re: Querying table permissions to see which users have them
…for role and sys privileges.
C##SCOTT@XE_21.3.0.0.0> CREATE USER C##SOMEBODY IDENTIFIED BY SOMEBODY QUOTA UNLIMITED ON USERS
2 /
User created.
C##SCOTT@XE_21.3.0.0.0> GRANT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE TO…
|
| 8 |
Forum: SQL & PL/SQL «»
Posted on: Fri, 24 November 2023 15:57 «»
By: Barbara Boehmer
Re: Sequentially update a column with a decrementing value
…
SCOTT@orcl_12.1.0.2.0> -- optional SQL*Plus format commands
SCOTT@orcl_12.1.0.2.0> -- to shorten column widths for
SCOTT@orcl_12.1.0.2.0> -- easier readability on this forum:
SCOTT@orcl_12.1.0.2.0> COLUMN LOC &…
|
| 9 |
Forum: Server Utilities «»
Posted on: Tue, 16 April 2024 08:17 «»
By: Barbara Boehmer
Re: impd data only on the same database from one table to another table
… link, then it works, as shown below.
SCOTT@orcl_12.1.0.2.0> CONN scott/tiger@orcl
Connected.
SCOTT@orcl_12.1.0.2.0> CREATE DATABASE LINK mylink CONNECT TO scott IDENTIFIED BY tiger USING 'ORCL'
2 /
Database link created…
|
| 10 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 05 April 2023 12:25 «»
By: Barbara Boehmer
Re: difficult task
… procedure, this is what you have so far.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PROCEDURE calculate_positions(
2 vInDateTime IN DATE;
3 )
4 AS
5 BEGIN
6 DELETE FROM positions;
7 END;
8 /
Warning: Procedure …
|
| 11 |
Forum: SQL & PL/SQL «»
Posted on: Thu, 05 October 2023 15:11 «»
By: Barbara Boehmer
Re: Need a pl/sql block to be entered in existing package
… statement that you should have provided:
SCOTT@orcl_12.1.0.2.0> create table mytable
2 (no number,
3 groups varchar2(11),
4 fromdate date,
5 todate …
|
| 12 |
Forum: Text & interMedia «»
Posted on: Mon, 14 August 2023 15:03 «»
By: Barbara Boehmer
Re: How to determine last tiime Content was indexed
…the resulting test.txt on my system.
C##SCOTT@XE_21.3.0.0.0> commit;
Commit complete.
C##SCOTT@XE_21.3.0.0.0> DROP INDEX kjc_test_index_url1 FORCE;
Index dropped.
C##SCOTT@XE_21.3.0.0.0> DROP TABLE KJC_TEST_INDEX;
Table dropped…
|
| 13 |
Forum: Oracle Fusion Apps & E-Business Suite «»
Posted on: Thu, 16 February 2023 00:33 «»
By: Barbara Boehmer
Re: function call with optional parameters
…or other things outside the function.
SCOTT@orcl_12.1.0.2.0> -- table and sample data for testing
SCOTT@orcl_12.1.0.2.0> CREATE TABLE pw_projnums_v AS
2 SELECT 1 AS segment1, 'PROJ1' AS class_code FROM DUAL UNION ALL
3 SELECT 2 AS …
|
| 14 |
Forum: SQL & PL/SQL «»
Posted on: Mon, 17 July 2023 13:13 «»
By: Solomon Yakobson
Re: DDL generation
… dba_constraints
where owner = 'SCOTT'
and table_name in ('PARENT','CHILD')
/
DDL
-----------------------------------------------------------------------------------
ALTER TABLE "SCOTT"."CHILD…
|
| 15 |
Forum: SQL & PL/SQL «»
Posted on: Mon, 01 May 2023 13:41 «»
By: Barbara Boehmer
Re: Join purchases to customers
… large enough to see a difference.
C##SCOTT@XE_21.3.0.0.0> -- addition of indexes and gathering of statistics for testing:
C##SCOTT@XE_21.3.0.0.0> CREATE INDEX c_idx ON customers (customer_id, last_name, first_name)
2 /
…
|
| 16 |
Forum: SQL & PL/SQL «»
Posted on: Thu, 28 September 2023 11:33 «»
By: Barbara Boehmer
Re: Inserted JSON into Column is being Truncated
…data that I copied into the file.
C##SCOTT@XE_21.3.0.0.0> -- load test data from file test.dat into clob variable g_clob
C##SCOTT@XE_21.3.0.0.0> -- in order to have the data in a clob that can be passed to your code
C##SCOTT@XE_21.3.0.0.0>…
|
| 17 |
Forum: SQL & PL/SQL «»
Posted on: Sat, 24 June 2023 13:19 «»
By: Barbara Boehmer
Re: How create such attached table by travelling view and its dependent objects
…it give you the results you wanted?
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PROCEDURE get_dependencies
2 ( p_start IN VARCHAR2 )
3 AS
4 v_levels NUMBER;
&…
|
| 18 |
Forum: Forms «»
Posted on: Wed, 17 May 2023 15:02 «»
By: Littlefoot
Re: table right user
…user FAHIM should see all SCOTT's tables, you could write code to write code for you.
For example, connected as SCOTT run the following PL/SQL block, spool output into a .SQL script and run it when connected as FAHIM.
SQL> spool fahim.sql
SQL…
|
| 19 |
Forum: SQL & PL/SQL «»
Posted on: Tue, 21 March 2023 03:38 «»
By: Barbara Boehmer
Re: convert xml column value into columns
…
get sql_id and plan_hash for testing:
SCOTT@orcl_12.1.0.2.0> column sql_id new_value sql_id
SCOTT@orcl_12.1.0.2.0> column plan_hash new_value plan_hash
SCOTT@orcl_12.1.0.2.0> select sql_id, '1' plan_hash
2 from dba_hist_sql_plan…
|
| 20 |
Forum: SQL & PL/SQL «»
Posted on: Fri, 17 February 2023 17:42 «»
By: Barbara Boehmer
Re: Cast(Multiset())
…
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE type Template as object(template_text varchar2(100),
2 template_value varchar2(100));
3 /
Type created.
SCOTT@orcl_12.1.0.2.0>
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE type …
|
| 21 |
Forum: SQL & PL/SQL «»
Posted on: Thu, 16 February 2023 15:40 «»
By: Barbara Boehmer
Re: Cast(Multiset())
…mn. Please see the modification below.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE type Template as object(template_text varchar2(100),
2 template_value varchar2(100));
3 /
Type created.
SCOTT@orcl_12.1.0.2.0>
SCOTT@…
|
| 22 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 15 February 2023 23:08 «»
By: Barbara Boehmer
Re: Cast(Multiset())
…comment lines along the left margin.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE type Template as object(template_text varchar2(100),
2 template_value varchar2(100));
3 /
Type created.
SCOTT@orcl_12.1.0.2.0>
SCOTT@…
|
| 23 |
Forum: Text & interMedia «»
Posted on: Mon, 16 January 2023 13:28 «»
By: Barbara Boehmer
Re: Materialized View with Context Index
…after commit using these methods.
SCOTT@orcl_12.1.0.2.0> CREATE MATERIALIZED VIEW LOG ON emp
2 /
Materialized view log created.
SCOTT@orcl_12.1.0.2.0> CREATE MATERIALIZED VIEW DIRECTORY_MV
2 (ID,
3 FIRST_NAME,
4 …
|
| 24 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 17 May 2023 15:34 «»
By: Barbara Boehmer
Re: check all the distinct value match
…chooses a different compound index.
SCOTT@orcl_12.1.0.2.0> CREATE INDEX num_idx ON call_details (call_number)
2 /
Index created.
SCOTT@orcl_12.1.0.2.0> CREATE INDEX typ_idx ON call_details (call_type)
2 /
…
|
| 25 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 08 February 2023 20:27 «»
By: Barbara Boehmer
Re: Convert Date formats
…you have the following table and data
SCOTT@orcl_12.1.0.2.0> create table mytable (date2 date)
2 /
Table created.
SCOTT@orcl_12.1.0.2.0> insert into mytable (date2) values (to_date ('25-JAN-23', 'DD-MON-YY'))
2 /
1 row created…
|
| 26 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 08 February 2023 17:00 «»
By: Barbara Boehmer
Re: Convert Date formats
…nls_date_format as demonstrated below.
SCOTT@orcl_12.1.0.2.0> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS'
2 /
Session altered.
SCOTT@orcl_12.1.0.2.0> SELECT SYSDATE FROM DUAL
2 /
SYSDATE
-------------------
2023…
|
| 27 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 01 May 2024 09:36 «»
By: Barbara Boehmer
Re: PL SQL table generate auto index
…nbsp;Please see the example below.
C##SCOTT@XE_21.3.0.0.0> create or replace TYPE emp_rec IS object(
2 ename varchar2(100),
3 sal number,
4 comm number);
…
|
| 28 |
Forum: SQL & PL/SQL «»
Posted on: Tue, 08 August 2023 14:26 «»
By: Barbara Boehmer
Re: Last N TRANSACTIONS for each customer (syntax error)
…execution plan, as shown below.
C##SCOTT@XE_21.3.0.0.0> create index customers_idx on customers (customer_id, first_name, last_name)
2 /
Index created.
C##SCOTT@XE_21.3.0.0.0> create index purchases_idx on purchases (…
|
| 29 |
Forum: SQL & PL/SQL «»
Posted on: Fri, 27 January 2023 11:23 «»
By: Barbara Boehmer
Re: Two Column Join
…asking for using your where clauses.
SCOTT@orcl_12.1.0.2.0> create table tab1 as select ename, comm col1 from emp where comm is not null
2 /
Table created.
SCOTT@orcl_12.1.0.2.0> create table tab2 as select ename, sal col2 from emp …
|
| 30 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 17 April 2024 16:46 «»
By: Barbara Boehmer
Re: Oracle ORA-00918: column ambiguously defined
…derived from data or supplied aliases:
SCOTT@orcl_12.1.0.2.0> select 1, 1 from dual
2 /
1 1
---------- ----------
…
|
| 31 |
Forum: SQL & PL/SQL «»
Posted on: Sat, 15 July 2023 11:19 «»
By: Michel Cadot
Re: DDL generation
…
4 WHERE OWNER = 'SCOTT' and TABLE_NAME in ('EMP','DEPT')
5 /
DBMS_METADATA.GET_DDL(DECODE(CONSTRAINT_TYPE,'R','REF_CONSTRAINT','CONSTRAINT'),CONSTRAINT_NAME,OWNER…
|
| 32 |
Forum: SQL & PL/SQL «»
Posted on: Thu, 22 June 2023 15:23 «»
By: Barbara Boehmer
Re: How create such attached table by travelling view and its dependent objects
…many levels there are in your data.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PROCEDURE get_dependencies
2 ( p_start IN VARCHAR2 )
3 AS
4 v_levels NUMBER;
&…
|
| 33 |
Forum: SQL & PL/SQL «»
Posted on: Mon, 30 October 2023 08:09 «»
By: Barbara Boehmer
Re: Create a oracle function that parse a string to date time when not null
…of function without any errors:
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE FUNCTION my_to_date(p_str IN VARCHAR2)
2 RETURN VARCHAR2
3 IS
4 v_date VARCHAR2(10);
5 BEGIN
&…
|
| 34 |
Forum: Performance Tuning «»
Posted on: Sat, 03 June 2023 03:30 «»
By: Barbara Boehmer
Re: Bind by position
…the following:
bind by position:
SCOTT@orcl_12.1.0.2.0> var x1 refcursor
SCOTT@orcl_12.1.0.2.0> begin
2 open :x1 for
3 'select * from emp where deptno in (:x, :x, :x) order by deptno, …
|
| 35 |
Forum: SQL & PL/SQL «»
Posted on: Tue, 28 February 2023 10:35 «»
By: Barbara Boehmer
Re: how to incrementally get all numbers incremented by n between 2 columns values
…
SCOTT@orcl_12.1.0.2.0> -- test_data:
SCOTT@orcl_12.1.0.2.0> select * from test_data order by instance_number, snap_id
2 /
INSTANCE_NUMBER SNAP_ID
--------------- ----------
1 165949
1 165950…
|
| 36 |
Forum: SQL & PL/SQL «»
Posted on: Thu, 16 February 2023 09:49 «»
By: Barbara Boehmer
Re: Cast(Multiset())
…others will comment on that as well.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE procedure Parse (RefCursor in out sys_refcursor)
2 is
3 v_pidm NUMBER;
4 v_first_name VARCHAR2(15);
5 v_last_name VARCHAR2(15);
6 …
|
| 37 |
Forum: Text & interMedia «»
Posted on: Mon, 16 January 2023 11:28 «»
By: Barbara Boehmer
Re: Oracle Text Search - Handling special characters and blank search term
…them with spaces, as shown below.
SCOTT@orcl_12.1.0.2.0> VARIABLE P2_SEARCH_TERM VARCHAR2(30)
SCOTT@orcl_12.1.0.2.0> EXEC :P2_SEARCH_TERM := 'GPT-3'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> SELECT TRANSLATE (:…
|
| 38 |
Forum: Text & interMedia «»
Posted on: Tue, 10 January 2023 12:15 «»
By: Barbara Boehmer
Re: Oracle Text Search - Handling special characters and blank search term
… or within a separate select statement.
SCOTT@orcl_12.1.0.2.0> VARIABLE P2_SEARCH_TERM VARCHAR2(30)
SCOTT@orcl_12.1.0.2.0> EXEC :P2_SEARCH_TERM := 'CLOUD'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> SELECT …
|
| 39 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 17 April 2024 17:31 «»
By: Barbara Boehmer
Re: Oracle ORA-00918: column ambiguously defined
…with factors from original post:
C##SCOTT@XE_21.3.0.0.0> create table test_tab
2 (col0 number generated by default as identity (start with 1) not null,
3 col1 varchar2(4),
4 col2 varchar2(4…
|
| 40 |
Forum: SQL & PL/SQL «»
Posted on: Tue, 16 April 2024 10:52 «»
By: Barbara Boehmer
Re: Convert input from user into UPPERCASE
…method that works from SQL*Plus:
SCOTT@orcl_12.1.0.2.0> COLUMN muser NEW_VALUE v_user NOPRINT
SCOTT@orcl_12.1.0.2.0> ACCEPT muser CHAR PROMPT 'Give username :'
Give username :Test123
SCOTT@orcl_12.1.0.2.0> SELECT UPPER('&&muser…
|
Blog
Search
Help
Members
Register
Login
Home